 /*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package gcs.Dados;

import gcs.Negocio.Cliente;
import gcs.Negocio.Contracto;
import gcs.Negocio.Historico;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.TreeMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Pedro
 */
public class ClientesBD {
    Connection con;
    ConnDB c;
    ContratoDB contractos = new ContratoDB();

    public ClientesBD(){
       try {
           con = new ConnDB().Connect();
           //empresas
           Statement st = con.createStatement();
           ResultSet rs = st.executeQuery("SELECT COUNT(cod_cliente) FROM Clientes");
           while (rs.next()) {
               Cliente.setContador(rs.getInt(1));
           }
           //Servicos
           st = con.createStatement();
           rs = st.executeQuery("SELECT COUNT(cod_historico) FROM Historico");
           while (rs.next()) {
               Historico.setContador(rs.getInt(1));
           }
       } catch (SQLException ex) {
           Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
       }
   }
    
    public TreeMap<String,Cliente> getListaCli(){
        TreeMap<String,Cliente> lc = new TreeMap<String,Cliente>();
        Statement st;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM clientes");
            while (rs.next()){
                Cliente c= new Cliente(true);
                c.setCodCliente(rs.getString(1));
                c.setNome(rs.getString(2));
                c.setNumBI(rs.getInt(3));
                c.setNumContribuinte(rs.getInt(4));
                c.setMorada(rs.getString(5));
                c.setActivo(rs.getBoolean(6));

                TreeMap<String,Contracto> lCont = new TreeMap<String,Contracto>(contractos.getContrCli(c.getCodCliente()));
                c.setContractos(lCont);
                c.setHist(getHistorico(c.getCodCliente()));

                lc.put(rs.getString(1),c);
                }
            }
        catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lc;
    }
  
    //ESCREVER
    //guarda um cliente
    public void guardaCliente(Cliente c){
        try{
            Statement st = con.createStatement();

            String cod = c.getCodCliente();
            String nome = c.getNome();
            int numBi = c.getNumBI();
            int numCont = c.getNumContribuinte();
            String morada = c.getMorada();
            boolean activo = c.isActivo();

            st.executeUpdate("INSERT INTO Clientes " +
            "VALUES ('"+cod+"','"+nome+"','"+numBi+"','"+numCont+"','"+morada+"',"+activo+")");
        }
        catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateCliente(Cliente c){
        try {
            Statement st = con.createStatement();
            String cod = c.getCodCliente();
            String nome = c.getNome();
            int bi = c.getNumBI();
            int cont = c.getNumContribuinte();
            String morada = c.getMorada();
            boolean activo = c.isActivo();
            st.executeUpdate("UPDATE Clientes SET " + "cod_cliente='" + cod + "'," + "nome='" + nome + "'," + "numBI='" + bi + "'," + "numContrib='" + cont + "'," + "morada='" + morada + "'," + "activo=" + activo + " WHERE cod_cliente ='" + cod + "'");
        } catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void insereHist(Cliente c ,Historico h){// String cod_cliente ?
        try{
            Statement st = con.createStatement();

            String cod_c = c.getCodCliente();
            String cod_h = h.getCodHistorico();
            String hist = h.getHist();
            st.executeUpdate("INSERT INTO Historico " +
            "VALUES ('"+cod_h+"','"+hist+"','"+cod_c+"')");
        }
        catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateHistorico(Historico h){
        try{
            Statement st = con.createStatement();

            String cod = h.getCodHistorico();
            String desc = h.getHist();
            st.executeUpdate("UPDATE Historico SET "+
                    "descricao='"+desc+"' WHERE cod_historico ='"+cod+"'");
        }
        catch (SQLException ex) {
            Logger.getLogger(EmpresasBD.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public void removeHistorico(Historico h){
        try{
            Statement st = con.createStatement();

            String cod = h.getCodHistorico();
            String desc = h.getHist();
            st.executeUpdate("DELETE FROM Historico WHERE cod_historico ='"+cod+"'");
        }
        catch (SQLException ex) {
            Logger.getLogger(EmpresasBD.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

   public ArrayList<Historico> getHistorico(String cod_c){ //Cliente c
        ArrayList<Historico> lh = new ArrayList<Historico>();
        Historico h;
        Statement st;
        try {
            st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM Historico WHERE cod_c = '"+cod_c+"'");
            while (rs.next()){
                h = new Historico(true);
                h.setCodHistorico(rs.getString(1));
                h.setHist(rs.getString(2));
                lh.add(h);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ClientesBD.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lh;
    }



}
